--    Program: 		CAPPOIMP_PURCHASE_ORDER_HEADER.sql
--    Description:  Import PO Headers All information 
--    Revision:     1st     
--    Parameter: 	
--    Created By:   Kenny Leung
--    Date:         23-Jan-2006

set doc off;      
set verify off;
set serveroutput on size 1000000;
whenever sqlerror exit failure rollback;

declare   
   l_processed_count		number;
   l_rejected_count 		number;
   l_err_msg				varchar2(2000);
   l_err_msg_tmp    		varchar2(2000);
   
   l_po_header_id			number;
                    		                 		
   cursor cur_headers_all is
      select * from CRST_PO_HEADERS_ALL for update;

begin
   l_processed_count := 0;
   l_rejected_count  := 0;
   l_err_msg := '';
   
   for cur in cur_headers_all loop   	
     l_err_msg_tmp := '';
     
     select PO_HEADERS_S.NEXTVAL 
     into l_po_header_id
     FROM DUAL;
     
     update CRST_PO_HEADERS_ALL
     set    po_header_id_n = l_po_header_id
     where  po_header_id = cur.PO_HEADER_ID;
     
     begin
       -- import data
       insert into PO_HEADERS_ALL 
       (
            PO_HEADER_ID                ,
            AGENT_ID                    ,
            TYPE_LOOKUP_CODE            ,
            LAST_UPDATE_DATE            ,
            LAST_UPDATED_BY            	,
            SEGMENT1                    ,
            SUMMARY_FLAG                ,
            ENABLED_FLAG                ,
            SEGMENT2                    ,
            SEGMENT3                    ,
            SEGMENT4                    ,
            SEGMENT5                    ,
            START_DATE_ACTIVE           ,
            END_DATE_ACTIVE             ,
            LAST_UPDATE_LOGIN           ,
            CREATION_DATE               ,
            CREATED_BY                  ,
            VENDOR_ID                   ,
            VENDOR_SITE_ID              ,
            VENDOR_CONTACT_ID           ,
            SHIP_TO_LOCATION_ID         ,
            BILL_TO_LOCATION_ID         ,
            TERMS_ID                    ,
            SHIP_VIA_LOOKUP_CODE        ,
            FOB_LOOKUP_CODE             ,
            FREIGHT_TERMS_LOOKUP_CODE   ,
            STATUS_LOOKUP_CODE          ,
            CURRENCY_CODE               ,
            RATE_TYPE                   ,
            RATE_DATE                   ,
            RATE                        ,
            FROM_HEADER_ID              ,
            FROM_TYPE_LOOKUP_CODE       ,
            START_DATE                  ,
            END_DATE                    ,
            BLANKET_TOTAL_AMOUNT        ,
            AUTHORIZATION_STATUS        ,
            REVISION_NUM                ,
            REVISED_DATE                ,
            APPROVED_FLAG               ,
            APPROVED_DATE               ,
            AMOUNT_LIMIT                ,
            MIN_RELEASE_AMOUNT          ,
            NOTE_TO_AUTHORIZER          ,
            NOTE_TO_VENDOR              ,
            NOTE_TO_RECEIVER            ,
            PRINT_COUNT                 ,
            PRINTED_DATE                ,
            VENDOR_ORDER_NUM            ,
            CONFIRMING_ORDER_FLAG       ,
            COMMENTS                    ,
            REPLY_DATE                  ,
            REPLY_METHOD_LOOKUP_CODE    ,
            RFQ_CLOSE_DATE              ,
            QUOTE_TYPE_LOOKUP_CODE      ,
            QUOTATION_CLASS_CODE        ,
            QUOTE_WARNING_DELAY_UNIT    ,
            QUOTE_WARNING_DELAY         ,
            QUOTE_VENDOR_QUOTE_NUMBER   ,
            ACCEPTANCE_REQUIRED_FLAG    ,
            ACCEPTANCE_DUE_DATE         ,
            CLOSED_DATE                 ,
            USER_HOLD_FLAG              ,
            APPROVAL_REQUIRED_FLAG      ,
            CANCEL_FLAG                 ,
            FIRM_STATUS_LOOKUP_CODE     ,
            FIRM_DATE                   ,
            FROZEN_FLAG                 ,
            SUPPLY_AGREEMENT_FLAG       ,
            EDI_PROCESSED_FLAG          ,
            EDI_PROCESSED_STATUS        ,
            ATTRIBUTE_CATEGORY          ,
            ATTRIBUTE1                  ,
            ATTRIBUTE2                  ,
            ATTRIBUTE3                  ,
            ATTRIBUTE4                  ,
            ATTRIBUTE5                  ,
            ATTRIBUTE6                  ,
            ATTRIBUTE7                  ,
            ATTRIBUTE8                  ,
            ATTRIBUTE9                  ,
            ATTRIBUTE10                 ,
            ATTRIBUTE11                 ,
            ATTRIBUTE12                 ,
            ATTRIBUTE13                 ,
            ATTRIBUTE14                 ,
            ATTRIBUTE15                 ,
            CLOSED_CODE                 ,
            USSGL_TRANSACTION_CODE      ,
            GOVERNMENT_CONTEXT          ,
            REQUEST_ID                  ,
            PROGRAM_APPLICATION_ID      ,
            PROGRAM_ID                  ,
            PROGRAM_UPDATE_DATE         ,
            ORG_ID                      ,
            GLOBAL_ATTRIBUTE_CATEGORY   ,
            GLOBAL_ATTRIBUTE1           ,
            GLOBAL_ATTRIBUTE2           ,
            GLOBAL_ATTRIBUTE3           ,
            GLOBAL_ATTRIBUTE4           ,
            GLOBAL_ATTRIBUTE5           ,
            GLOBAL_ATTRIBUTE6           ,
            GLOBAL_ATTRIBUTE7           ,
            GLOBAL_ATTRIBUTE8           ,
            GLOBAL_ATTRIBUTE9           ,
            GLOBAL_ATTRIBUTE10          ,
            GLOBAL_ATTRIBUTE11          ,
            GLOBAL_ATTRIBUTE12          ,
            GLOBAL_ATTRIBUTE13          ,
            GLOBAL_ATTRIBUTE14          ,
            GLOBAL_ATTRIBUTE15          ,
            GLOBAL_ATTRIBUTE16          ,
            GLOBAL_ATTRIBUTE17          ,
            GLOBAL_ATTRIBUTE18          ,
            GLOBAL_ATTRIBUTE19          ,
            GLOBAL_ATTRIBUTE20          ,
            INTERFACE_SOURCE_CODE       ,
            REFERENCE_NUM               ,
            WF_ITEM_TYPE                ,
            WF_ITEM_KEY                 ,
            MRC_RATE_TYPE               ,
            MRC_RATE_DATE               ,
            MRC_RATE                    ,
            PCARD_ID                    ,
            PRICE_UPDATE_TOLERANCE      ,
            PAY_ON_CODE                 ,
            XML_FLAG                    ,
            XML_SEND_DATE               ,
            XML_CHANGE_SEND_DATE        ,
            GLOBAL_AGREEMENT_FLAG       ,
            CONSIGNED_CONSUMPTION_FLAG  ,
            CBC_ACCOUNTING_DATE         ,
            CONSUME_REQ_DEMAND_FLAG     ,
            CHANGE_REQUESTED_BY         
       )
       values
       (
            l_po_header_id                  ,
            CAPBRIMP_MAPPING.GetEmployeeMapping(cur.AGENT_ID),
            cur.TYPE_LOOKUP_CODE            ,
            cur.LAST_UPDATE_DATE            ,
            --LAST_UPDATED_BY					,
            CUR.LAST_UPDATED_BY				,
            cur.SEGMENT1                    ,
            cur.SUMMARY_FLAG                ,
            cur.ENABLED_FLAG                ,
            cur.SEGMENT2                    ,
            cur.SEGMENT3                    ,
            cur.SEGMENT4                    ,
            cur.SEGMENT5                    ,
            cur.START_DATE_ACTIVE           ,
            cur.END_DATE_ACTIVE             ,
            cur.LAST_UPDATE_LOGIN           ,
            cur.CREATION_DATE               ,
            CAPBRIMP_MAPPING.GetUserMapping(cur.CREATED_BY),
            CAPPOIMP_MAPPING.GetVendorIDMapping(cur.VENDOR_ID),
            CAPPOIMP_MAPPING.GetVendorSiteIDMapping(cur.VENDOR_SITE_ID),
            CAPPOIMP_MAPPING.GetVendorContactIDMapping(cur.VENDOR_CONTACT_ID),
            CAPPOIMP_MAPPING.GetHRLocationIDMapping(cur.SHIP_TO_LOCATION_ID),
            CAPPOIMP_MAPPING.GetHRLocationIDMapping(cur.BILL_TO_LOCATION_ID),
            cur.TERMS_ID                    ,
            cur.SHIP_VIA_LOOKUP_CODE        ,
            cur.FOB_LOOKUP_CODE             ,
            cur.FREIGHT_TERMS_LOOKUP_CODE   ,
            cur.STATUS_LOOKUP_CODE          ,
            cur.CURRENCY_CODE               ,
            cur.RATE_TYPE                   ,
            cur.RATE_DATE                   ,
            cur.RATE                        ,
            cur.FROM_HEADER_ID              ,
            cur.FROM_TYPE_LOOKUP_CODE       ,
            cur.START_DATE                  ,
            cur.END_DATE                    ,
            cur.BLANKET_TOTAL_AMOUNT        ,
            cur.AUTHORIZATION_STATUS        ,
            cur.REVISION_NUM                ,
            cur.REVISED_DATE                , 
            cur.APPROVED_FLAG               , 
            cur.APPROVED_DATE               , 
            cur.AMOUNT_LIMIT                , 
            cur.MIN_RELEASE_AMOUNT          , 
            cur.NOTE_TO_AUTHORIZER          , 
            cur.NOTE_TO_VENDOR              , 
            cur.NOTE_TO_RECEIVER            , 
            cur.PRINT_COUNT                 , 
            cur.PRINTED_DATE                , 
            cur.VENDOR_ORDER_NUM            , 
            cur.CONFIRMING_ORDER_FLAG       , 
            cur.COMMENTS                    , 
            cur.REPLY_DATE                  , 
            cur.REPLY_METHOD_LOOKUP_CODE    , 
            cur.RFQ_CLOSE_DATE              , 
            cur.QUOTE_TYPE_LOOKUP_CODE      , 
            cur.QUOTATION_CLASS_CODE        , 
            cur.QUOTE_WARNING_DELAY_UNIT    , 
            cur.QUOTE_WARNING_DELAY         , 
            cur.QUOTE_VENDOR_QUOTE_NUMBER   , 
            cur.ACCEPTANCE_REQUIRED_FLAG    , 
            cur.ACCEPTANCE_DUE_DATE         , 
            cur.CLOSED_DATE                 , 
            cur.USER_HOLD_FLAG              , 
            cur.APPROVAL_REQUIRED_FLAG      , 
            cur.CANCEL_FLAG                 , 
            cur.FIRM_STATUS_LOOKUP_CODE     , 
            cur.FIRM_DATE                   , 
            cur.FROZEN_FLAG                 , 
            cur.SUPPLY_AGREEMENT_FLAG       , 
            cur.EDI_PROCESSED_FLAG          , 
            cur.EDI_PROCESSED_STATUS        , 
            cur.ATTRIBUTE_CATEGORY          , 
            cur.ATTRIBUTE1                  , 
            cur.ATTRIBUTE2                  , 
            cur.ATTRIBUTE3                  , 
            cur.ATTRIBUTE4                  , 
            cur.ATTRIBUTE5                  , 
            cur.ATTRIBUTE6                  , 
            cur.ATTRIBUTE7                  , 
            cur.ATTRIBUTE8                  , 
            cur.ATTRIBUTE9                  , 
            cur.ATTRIBUTE10                 , 
            cur.ATTRIBUTE11                 , 
            cur.ATTRIBUTE12                 , 
            cur.ATTRIBUTE13                 , 
            cur.ATTRIBUTE14                 , 
            cur.ATTRIBUTE15                 , 
            cur.CLOSED_CODE                 , 
            cur.USSGL_TRANSACTION_CODE      , 
            cur.GOVERNMENT_CONTEXT          , 
            cur.REQUEST_ID                  , 
            cur.PROGRAM_APPLICATION_ID      , 
            cur.PROGRAM_ID                  , 
            cur.PROGRAM_UPDATE_DATE         , 
            CAPBRIMP_MAPPING.GetOrgMapping(cur.ORG_ID),
            cur.GLOBAL_ATTRIBUTE_CATEGORY   , 
            cur.GLOBAL_ATTRIBUTE1           , 
            cur.GLOBAL_ATTRIBUTE2           , 
            cur.GLOBAL_ATTRIBUTE3           , 
            cur.GLOBAL_ATTRIBUTE4           , 
            cur.GLOBAL_ATTRIBUTE5           , 
            cur.GLOBAL_ATTRIBUTE6           , 
            cur.GLOBAL_ATTRIBUTE7           , 
            cur.GLOBAL_ATTRIBUTE8           , 
            cur.GLOBAL_ATTRIBUTE9           , 
            cur.GLOBAL_ATTRIBUTE10          , 
            cur.GLOBAL_ATTRIBUTE11          , 
            cur.GLOBAL_ATTRIBUTE12          , 
            cur.GLOBAL_ATTRIBUTE13          , 
            cur.GLOBAL_ATTRIBUTE14          , 
            cur.GLOBAL_ATTRIBUTE15          , 
            cur.GLOBAL_ATTRIBUTE16          , 
            cur.GLOBAL_ATTRIBUTE17          , 
            cur.GLOBAL_ATTRIBUTE18          , 
            cur.GLOBAL_ATTRIBUTE19          , 
            cur.GLOBAL_ATTRIBUTE20          , 
            cur.INTERFACE_SOURCE_CODE       , 
            cur.REFERENCE_NUM               , 
            cur.WF_ITEM_TYPE                , 
            cur.WF_ITEM_KEY                 , 
            cur.MRC_RATE_TYPE               , 
            cur.MRC_RATE_DATE               , 
            cur.MRC_RATE                    , 
            cur.PCARD_ID                    , 
            cur.PRICE_UPDATE_TOLERANCE      , 
            cur.PAY_ON_CODE                 , 
            cur.XML_FLAG                    , 
            cur.XML_SEND_DATE               , 
            cur.XML_CHANGE_SEND_DATE        , 
            cur.GLOBAL_AGREEMENT_FLAG       , 
            cur.CONSIGNED_CONSUMPTION_FLAG  , 
            cur.CBC_ACCOUNTING_DATE         , 
            cur.CONSUME_REQ_DEMAND_FLAG     , 
            cur.CHANGE_REQUESTED_BY           
       );  
     exception
       when others then
     	 l_err_msg_tmp := sqlerrm;
     end;	  	    
     
     if l_err_msg_tmp IS NULL then
       l_processed_count := l_processed_count + 1;  
     else
       l_rejected_count := l_rejected_count + 1;
       l_err_msg := l_err_msg_tmp; 
     end if;
   end loop;

   if l_rejected_count = 0 then
   	 commit;
   else
     rollback;
   end if;
   
   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
    values ('CAPPOIMP_PURCHASE_ORDER_HEADER', sysdate, l_processed_count, l_rejected_count, l_processed_count-l_rejected_count, l_err_msg );
   
   commit;
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;  
end;
/


--exit
